from flask import Flask, request, jsonify, render_template, redirect, url_for
from database import get_db_connection, init_db
import sqlite3
from datetime import datetime

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your-secret-key-here'

# 初始化数据库
init_db()


def todo_to_dict(todo):
    """将SQLite行对象转换为字典"""
    return {
        'id': todo['id'],
        'title': todo['title'],
        'description': todo['description'],
        'completed': bool(todo['completed']),
        'created_at': todo['created_at']
    }


# API 路由
@app.route('/api/todos', methods=['GET'])
def get_all_todos_api():
    """获取所有TODO项目 (API)"""
    try:
        conn = get_db_connection()
        todos = conn.execute('SELECT * FROM todos ORDER BY created_at DESC').fetchall()
        conn.close()

        todos_list = [todo_to_dict(todo) for todo in todos]
        return jsonify(todos_list), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route('/api/todos/<int:todo_id>', methods=['GET'])
def get_todo_api(todo_id):
    """获取单个TODO项目 (API)"""
    try:
        conn = get_db_connection()
        todo = conn.execute('SELECT * FROM todos WHERE id = ?', (todo_id,)).fetchone()
        conn.close()

        if todo is None:
            return jsonify({'error': 'TODO not found'}), 404

        return jsonify(todo_to_dict(todo)), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route('/api/todos', methods=['POST'])
def create_todo_api():
    """创建新的TODO项目 (API)"""
    try:
        data = request.get_json()

        # 验证必需字段
        if not data or 'title' not in data:
            return jsonify({'error': 'Title is required'}), 400

        title = data['title']
        description = data.get('description', '')
        completed = data.get('completed', False)

        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute(
            'INSERT INTO todos (title, description, completed) VALUES (?, ?, ?)',
            (title, description, 1 if completed else 0)
        )
        conn.commit()

        # 获取新创建的TODO
        new_todo_id = cursor.lastrowid
        new_todo = conn.execute('SELECT * FROM todos WHERE id = ?', (new_todo_id,)).fetchone()
        conn.close()

        return jsonify(todo_to_dict(new_todo)), 201
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route('/api/todos/<int:todo_id>', methods=['DELETE'])
def delete_todo_api(todo_id):
    """删除TODO项目 (API)"""
    try:
        conn = get_db_connection()

        # 先检查TODO是否存在
        todo = conn.execute('SELECT * FROM todos WHERE id = ?', (todo_id,)).fetchone()
        if todo is None:
            conn.close()
            return jsonify({'error': 'TODO not found'}), 404

        # 删除TODO
        conn.execute('DELETE FROM todos WHERE id = ?', (todo_id,))
        conn.commit()
        conn.close()

        return jsonify({'message': 'TODO deleted successfully'}), 200
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 网页路由
@app.route('/')
def index():
    """主页 - 显示所有TODO"""
    try:
        conn = get_db_connection()
        todos = conn.execute('SELECT * FROM todos ORDER BY created_at DESC').fetchall()
        conn.close()

        # 转换为字典列表供模板使用
        todos_list = [dict(todo) for todo in todos]
        return render_template('index.html', todos=todos_list)
    except Exception as e:
        return render_template('index.html', todos=[], error=str(e))


@app.route('/todos/<int:todo_id>')
def todo_detail(todo_id):
    """TODO详情页"""
    try:
        conn = get_db_connection()
        todo = conn.execute('SELECT * FROM todos WHERE id = ?', (todo_id,)).fetchone()
        conn.close()

        if todo is None:
            return render_template('todo_detail.html', error='TODO not found'), 404

        return render_template('todo_detail.html', todo=dict(todo))
    except Exception as e:
        return render_template('todo_detail.html', error=str(e)), 500


@app.route('/todos/create', methods=['GET', 'POST'])
def create_todo():
    """创建TODO页面"""
    if request.method == 'GET':
        return render_template('create_todo.html')

    elif request.method == 'POST':
        try:
            title = request.form.get('title')
            description = request.form.get('description', '')
            completed = 'completed' in request.form

            if not title:
                return render_template('create_todo.html', error='标题不能为空')

            conn = get_db_connection()
            cursor = conn.cursor()
            cursor.execute(
                'INSERT INTO todos (title, description, completed) VALUES (?, ?, ?)',
                (title, description, 1 if completed else 0)
            )
            conn.commit()
            conn.close()

            return redirect(url_for('index'))
        except Exception as e:
            return render_template('create_todo.html', error=str(e))


@app.route('/todos/<int:todo_id>/delete', methods=['POST'])
def delete_todo_web(todo_id):
    """删除TODO (网页版)"""
    try:
        conn = get_db_connection()

        # 先检查TODO是否存在
        todo = conn.execute('SELECT * FROM todos WHERE id = ?', (todo_id,)).fetchone()
        if todo is None:
            conn.close()
            return redirect(url_for('index'))

        # 删除TODO
        conn.execute('DELETE FROM todos WHERE id = ?', (todo_id,))
        conn.commit()
        conn.close()

        return redirect(url_for('index'))
    except Exception as e:
        return redirect(url_for('index'))


# 错误处理
@app.errorhandler(404)
def not_found(error):
    return render_template('404.html'), 404


@app.errorhandler(500)
def internal_error(error):
    return render_template('500.html'), 500


if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)